package techLayer;

import java.sql.Statement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import javax.swing.JOptionPane;

/* 
 * A class that connects to the highscore database.
 * It can add new players, check if an player already exists
 * aswell as add win/loss points for a specific player.
 * The getHighscore method returns an Iterator with every tuple
 * from the highscoretable.
 */
public class Database {
    private Connection connection;
    private Statement statement;
    private static final String USER = "dbtht1118", PASSWORD = "nafseq09";
    
    public Database(){
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");

        } catch (ClassNotFoundException ex) {
            System.out.println("Error: Driver not found!");
        }
        try {
            connection = DriverManager.getConnection("jdbc:sqlserver://idasql-db.hb.se:56001;"
                                                     + "user="+USER+";password="+PASSWORD);
            statement = connection.createStatement();
        } catch (SQLException ex) {
            System.out.println("Error: Couldn't connect to database!");
            JOptionPane.showMessageDialog(null, "Couldn't connect to database.", "Error", JOptionPane.ERROR_MESSAGE, null);
        }
    }
    public Iterator getHighscore(){
        List list = new ArrayList();
        try{
            ResultSet resultSet = statement.executeQuery("SELECT TOP 25 * FROM highscore ORDER BY wins DESC"); // LIMIT 25
            while(resultSet.next()){
                int wins = Integer.parseInt(resultSet.getString(2));
                int loss = Integer.parseInt(resultSet.getString(3));
                ScoreRow row = new ScoreRow(resultSet.getString(1), wins,loss);
                list.add(row);
            }
        }catch(SQLException ex){
            JOptionPane.showMessageDialog(null,"Error: "+ex.getMessage(), "Error", JOptionPane.ERROR_MESSAGE, null);
        }
        return list.iterator();
    }
    public void addPlayer(String name){
        try {
            statement.executeUpdate("INSERT INTO highscore VALUES('"+name+"',0,0)");
        } catch (SQLException ex) {
            JOptionPane.showMessageDialog(null,"Error: "+ex.getMessage(), "Error", JOptionPane.ERROR_MESSAGE, null);
        }
    }
    public Boolean playerExists(String name){
        try{
            ResultSet resultSet = statement.executeQuery("SELECT * FROM highscore");
            while(resultSet.next()){
                if(resultSet.getString(1).equals(name))
                    return true;
            } 
        }catch(SQLException ex){
            JOptionPane.showMessageDialog(null,"Error: "+ex.getMessage(), "Error", JOptionPane.ERROR_MESSAGE, null);
        }
        return false;
    }
    public void addWin(String name){
        
        try{
            ResultSet result = statement.executeQuery("SELECT wins FROM highscore WHERE name = '"+name+"'");
            
            result.next();
            int wins = Integer.parseInt(result.getString(1));
            wins++;
            statement.executeUpdate("UPDATE highscore SET wins = "+wins+" WHERE name = '"+name+"'");
            
        }catch(SQLException ex){
            JOptionPane.showMessageDialog(null,"Error: "+ex.getMessage(), "Error", JOptionPane.ERROR_MESSAGE, null);
        }
    }
    public void addLoss(String name){
        
        try{
            ResultSet result = statement.executeQuery("SELECT loss FROM highscore WHERE name = '"+name+"'");
            
            result.next();
            int loss = Integer.parseInt(result.getString(1));
            loss++;
            statement.executeUpdate("UPDATE highscore SET loss = "+loss+" WHERE name = '"+name+"'");
            
        }catch(SQLException ex){
            JOptionPane.showMessageDialog(null,"Error: "+ex.getMessage(), "Error", JOptionPane.ERROR_MESSAGE, null);
        }
    }
    public void close(){
        try {
            connection.close();
        } catch (SQLException ex) {
            JOptionPane.showMessageDialog(null,"Error: "+ex.getMessage(), "Error", JOptionPane.ERROR_MESSAGE, null);
        }
    }
}
